const nodemailer = require('nodemailer')
const mysql = require('mysql')
const { mysqlConfig } = require('./utils/db')
const moment = require('moment')


const transporter = nodemailer.createTransport({
  host: 'smtp.qq.com',
  port: 465,
  secure: true,
  auth: {
    user: '1572573908@qq.com',
    pass: 'bsaysshuedphjgja'
  }
})
const connection = mysql.createConnection(mysqlConfig)

connection.connect(function(error) {
  if (error) throw error
  console.log('数据库连接成功')
})

const dateToFilter = moment().subtract(1, 'days').format('YYYY-MM-DD')

const sqlQuery = 'SELECT * FROM require_goods'

const dealData = (list) => {
  const trArr = []
  list.forEach(item => {
    item.forEach((jtem, jndex) => {
      let rowspan = ''
      if (jndex === 0) {
        trArr.push(`
          <tr>
            <td rowspan="${item.length}">${jtem.goods_name}</td>
            <td>${jtem.goods_price}</td>
            <td>${moment(jtem.record_time).format('YYYY-MM-DD HH:mm:ss')}</td>
          </tr>
        `)
      } else {
        trArr.push(`
          <tr>
            <td>${jtem.goods_price}</td>
            <td>${moment(jtem.record_time).format('YYYY-MM-DD HH:mm:ss')}</td>
          </tr>
        `)
      }
    })
  })
  let html = `
    <h2>${dateToFilter}价格一览</h2>
    <table border="1">
      <tr>
          <th>名称</th>
          <th>价格</th>
          <th>时间</th>
      </tr>
  `
  trArr.forEach(item => {
    html += item
  })

  html += '</table>'
  console.log('htem:' ,html)
  const mailOptions = {
    from: '1572573908@qq.com',
    to: '1454056050@qq.com',
    subject: `${dateToFilter}数据`,
    html,
  }

  transporter.sendMail(mailOptions, (err, info) => {
    if (err) {
      console.log(err)
    } else {
      console.log('发送成功')
    }
  })
}

const dealEnd = (error) => {
  connection.end((err) => {
    if (err) throw err
    console.log('数据库连接已关闭')
  })
  if (error) throw error
}

const syncSql = (sql, values) => {
  return new Promise((resolve, reject) => {
    connection.query(sql, values, (error, results, fields) => {
      if (error) {
        reject(error)
      }
      resolve(results)
    })
  })
}

const dealResults = async (list) => {
  let allResults = []
  for (let index = 0; index < list.length; index++) {
    const nextSqlQuery = `SELECT * FROM goods_info WHERE DATE(record_time) = ? AND goods_id = ?`
    const values = [dateToFilter, list[index].goods_id]
    let result = await syncSql(nextSqlQuery, values)
    allResults.push(result)
  }
  return allResults
}

connection.query(sqlQuery, async (error, results, fields) => {
  if (error) throw error
  console.log('Query results:', results)
  let allResults = await dealResults(results)
  dealData(allResults)
  dealEnd()
})

// connection.query(sqlQuery, [dateToFilter], (error, results, fields) => {
//   // 释放连接回连接池
//   if (error) throw error

//   // 处理查询结果
//   console.log('Query results:', results)
//   // dealData(results)
//   connection.end((err) => {
//     if (err) throw err
//     console.log('数据库连接已关闭')
//   })
// })

